{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups based on school code. Also check the type of GroupBy object.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('display.max_rows', None)\n",
    "#pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151      31   \n",
       "S6        s004    VI    David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student_data = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s004'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'height': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "\n",
    "print(\"Original DataFrame:\")\n",
    "\n",
    "student_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Split the said data on school_code wise:\n",
      "\n",
      "Group:\n",
      "s001\n",
      "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
      "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
      "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
      "\n",
      "    address  \n",
      "S1  street1  \n",
      "S4  street1  \n",
      "\n",
      "Group:\n",
      "s002\n",
      "   school_code class          name date_Of_Birth   age  height  weight  \\\n",
      "S2        s002     V  Gino Mcneill     17/05/2002   12     192      32   \n",
      "S5        s002     V  Gino Mcneill     11/05/2002   14     151      31   \n",
      "\n",
      "    address  \n",
      "S2  street2  \n",
      "S5  street2  \n",
      "\n",
      "Group:\n",
      "s003\n",
      "   school_code class         name date_Of_Birth   age  height  weight  address\n",
      "S3        s003    VI  Ryan Parkes     16/02/1999   13     186      33  street3\n",
      "\n",
      "Group:\n",
      "s004\n",
      "   school_code class          name date_Of_Birth   age  height  weight  \\\n",
      "S6        s004    VI  David Parkes     15/09/1997   12     159      32   \n",
      "\n",
      "    address  \n",
      "S6  street4  \n",
      "\n",
      "Type of the object:\n",
      "<class 'pandas.core.groupby.generic.DataFrameGroupBy'>\n"
     ]
    }
   ],
   "source": [
    "print('\\nSplit the said data on school_code wise:')\n",
    "result = student_data.groupby(['school_code'])\n",
    "for name,group in result:\n",
    "    print(\"\\nGroup:\")\n",
    "    print(name)\n",
    "    print(group)\n",
    "print(\"\\nType of the object:\")\n",
    "print(type(result))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe by school code and get mean, min, and max value of age for each school.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Mean, min, and max value of age for each value of the school:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>s001</th>\n",
       "      <td>12.5</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s002</th>\n",
       "      <td>13.0</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s003</th>\n",
       "      <td>13.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s004</th>\n",
       "      <td>12.0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              age        \n",
       "             mean min max\n",
       "school_code              \n",
       "s001         12.5  12  13\n",
       "s002         13.0  12  14\n",
       "s003         13.0  13  13\n",
       "s004         12.0  12  12"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('\\nMean, min, and max value of age for each value of the school:')\n",
    "grouped_single = student_data.groupby('school_code').agg({'age': ['mean', 'min', 'max']})\n",
    "grouped_single"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following given dataframe into groups based on school code and class.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Split the said data on school_code, class wise:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x00000201B4146F48>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('\\nSplit the said data on school_code, class wise:')\n",
    "result = student_data.groupby(['school_code', 'class'])\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Group:\n",
      "('s001', 'V')\n",
      "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
      "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
      "\n",
      "    address  \n",
      "S1  street1  \n",
      "\n",
      "Group:\n",
      "('s001', 'VI')\n",
      "   school_code class          name date_Of_Birth   age  height  weight  \\\n",
      "S4        s001    VI  Eesha Hinton     25/09/1998   13     167      30   \n",
      "\n",
      "    address  \n",
      "S4  street1  \n",
      "\n",
      "Group:\n",
      "('s002', 'V')\n",
      "   school_code class          name date_Of_Birth   age  height  weight  \\\n",
      "S2        s002     V  Gino Mcneill     17/05/2002   12     192      32   \n",
      "S5        s002     V  Gino Mcneill     11/05/2002   14     151      31   \n",
      "\n",
      "    address  \n",
      "S2  street2  \n",
      "S5  street2  \n",
      "\n",
      "Group:\n",
      "('s003', 'VI')\n",
      "   school_code class         name date_Of_Birth   age  height  weight  address\n",
      "S3        s003    VI  Ryan Parkes     16/02/1999   13     186      33  street3\n",
      "\n",
      "Group:\n",
      "('s004', 'VI')\n",
      "   school_code class          name date_Of_Birth   age  height  weight  \\\n",
      "S6        s004    VI  David Parkes     15/09/1997   12     159      32   \n",
      "\n",
      "    address  \n",
      "S6  street4  \n"
     ]
    }
   ],
   "source": [
    "for name,group in result:\n",
    "    print(\"\\nGroup:\")\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following given dataframe into groups based on school code and cast grouping as a list.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('s001',    school_code class            name date_Of_Birth   age  height  weight  \\\n",
      "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
      "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
      "\n",
      "    address  \n",
      "S1  street1  \n",
      "S4  street1  ), ('s002',    school_code class          name date_Of_Birth   age  height  weight  \\\n",
      "S2        s002     V  Gino Mcneill     17/05/2002   12     192      32   \n",
      "S5        s002     V  Gino Mcneill     11/05/2002   14     151      31   \n",
      "\n",
      "    address  \n",
      "S2  street2  \n",
      "S5  street2  ), ('s003',    school_code class         name date_Of_Birth   age  height  weight  address\n",
      "S3        s003    VI  Ryan Parkes     16/02/1999   13     186      33  street3), ('s004',    school_code class          name date_Of_Birth   age  height  weight  \\\n",
      "S6        s004    VI  David Parkes     15/09/1997   12     159      32   \n",
      "\n",
      "    address  \n",
      "S6  street4  )]\n"
     ]
    }
   ],
   "source": [
    "result = student_data.groupby(['school_code'])\n",
    "print(list(result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S4  street1  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(result)[0][1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following given dataframe into groups based on single column and multiple columns. Find the size of the grouped data.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Split the said data on school_code wise:\n",
      "Size of the grouped data - single column\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "school_code\n",
       "s001    2\n",
       "s002    2\n",
       "s003    1\n",
       "s004    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('\\nSplit the said data on school_code wise:')\n",
    "grouped_single = student_data.groupby(['school_code'])\n",
    "print(\"Size of the grouped data - single column\")\n",
    "grouped_single.size()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size of the grouped data - multiple columns:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "school_code  class\n",
       "s001         V        1\n",
       "             VI       1\n",
       "s002         V        2\n",
       "s003         VI       1\n",
       "s004         VI       1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_mul = student_data.groupby(['school_code', 'class'])\n",
    "print(\"Size of the grouped data - multiple columns:\")\n",
    "grouped_mul.size()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following given dataframe into groups based on school code and call a specific group with the name of the group.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Call school code 's001':\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S4  street1  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = student_data.groupby(['school_code'])\n",
    "print(\"Call school code 's001':\")\n",
    "grouped.get_group('s001')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Call school code 's004':\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class          name date_Of_Birth   age  height  weight  \\\n",
       "S6        s004    VI  David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\nCall school code 's004':\")\n",
    "grouped.get_group('s004')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a dataset, group by one column and get mean, min, and max values by group. Using the following dataset find the mean, min, and max values of purchase amount (purch_amt) group by customer id (customer_id).\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3009</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2012-07-27</td>\n",
       "      <td>3007</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3004</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3009</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>2012-06-27</td>\n",
       "      <td>3008</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3003</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>2012-04-25</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  2012-10-05         3005         5002\n",
       "1    70009     270.65  2012-09-10         3001         5005\n",
       "2    70002      65.26  2012-10-05         3002         5001\n",
       "3    70004     110.50  2012-08-17         3009         5003\n",
       "4    70007     948.50  2012-09-10         3005         5002\n",
       "5    70005    2400.60  2012-07-27         3007         5001\n",
       "6    70008    5760.00  2012-09-10         3002         5001\n",
       "7    70010    1983.43  2012-10-10         3004         5006\n",
       "8    70003    2480.40  2012-10-10         3009         5003\n",
       "9    70012     250.45  2012-06-27         3008         5002\n",
       "10   70011      75.29  2012-08-17         3003         5007\n",
       "11   70013    3045.60  2012-04-25         3002         5001"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_data = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['2012-10-05','2012-09-10','2012-10-05','2012-08-17','2012-09-10','2012-07-27','2012-09-10','2012-10-10','2012-10-10','2012-06-27','2012-08-17','2012-04-25'],\n",
    "'customer_id':[3005,3001,3002,3009,3005,3007,3002,3004,3009,3008,3003,3002],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "\n",
    "orders_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Mean, min, and max values of purchase amount (purch_amt) group by customer id  (customer_id).\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">purch_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3001</th>\n",
       "      <td>270.650000</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3002</th>\n",
       "      <td>2956.953333</td>\n",
       "      <td>65.26</td>\n",
       "      <td>5760.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>1983.430000</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>549.500000</td>\n",
       "      <td>150.50</td>\n",
       "      <td>948.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>2400.600000</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2400.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3008</th>\n",
       "      <td>250.450000</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009</th>\n",
       "      <td>1295.450000</td>\n",
       "      <td>110.50</td>\n",
       "      <td>2480.40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               purch_amt                  \n",
       "                    mean      min      max\n",
       "customer_id                               \n",
       "3001          270.650000   270.65   270.65\n",
       "3002         2956.953333    65.26  5760.00\n",
       "3003           75.290000    75.29    75.29\n",
       "3004         1983.430000  1983.43  1983.43\n",
       "3005          549.500000   150.50   948.50\n",
       "3007         2400.600000  2400.60  2400.60\n",
       "3008          250.450000   250.45   250.45\n",
       "3009         1295.450000   110.50  2480.40"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = orders_data.groupby('customer_id').agg({'purch_amt': ['mean', 'min', 'max']})\n",
    "print(\"\\nMean, min, and max values of purchase amount (purch_amt) group by customer id  (customer_id).\")\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### In the following dataset group on 'customer_id', 'salesman_id' and then sort sum of purch_amt within the groups\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>purch_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">3001</th>\n",
       "      <th>5001</th>\n",
       "      <td>2400.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>400.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>110.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5005</th>\n",
       "      <td>270.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5006</th>\n",
       "      <td>1983.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">3005</th>\n",
       "      <th>5001</th>\n",
       "      <td>8870.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>948.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>2480.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5007</th>\n",
       "      <td>75.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         purch_amt\n",
       "customer_id salesman_id           \n",
       "3001        5001           2400.60\n",
       "            5002            400.95\n",
       "            5003            110.50\n",
       "            5005            270.65\n",
       "            5006           1983.43\n",
       "3005        5001           8870.86\n",
       "            5002            948.50\n",
       "            5003           2480.40\n",
       "            5007             75.29"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['2012-10-05','2012-09-10','2012-10-05','2012-08-17','2012-09-10','2012-07-27','2012-09-10','2012-10-10','2012-10-10','2012-06-27','2012-08-17','2012-04-25'],\n",
    "'customer_id':[3001,3001,3005,3001,3005,3001,3005,3001,3005,3001,3005,3005],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "df_agg = df.groupby(['customer_id','salesman_id']).agg({'purch_amt':sum})\n",
    "df_agg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Group on 'customer_id', 'salesman_id' and then sort sum of purch_amt within the groups:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "customer_id  salesman_id\n",
       "3001         5001           2400.60\n",
       "             5006           1983.43\n",
       "             5002            400.95\n",
       "             5005            270.65\n",
       "             5003            110.50\n",
       "3005         5001           8870.86\n",
       "             5003           2480.40\n",
       "             5002            948.50\n",
       "             5007             75.29\n",
       "Name: purch_amt, dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df_agg['purch_amt'].groupby(level=0, group_keys=False)\n",
    "print(\"\\nGroup on 'customer_id', 'salesman_id' and then sort sum of purch_amt within the groups:\")\n",
    "result.nlargest(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups based on customer id and create a list of order date for each group.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2012-07-27</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>2012-06-27</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>2012-04-25</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  2012-10-05         3001         5002\n",
       "1    70009     270.65  2012-09-10         3001         5005\n",
       "2    70002      65.26  2012-10-05         3005         5001\n",
       "3    70004     110.50  2012-08-17         3001         5003\n",
       "4    70007     948.50  2012-09-10         3005         5002\n",
       "5    70005    2400.60  2012-07-27         3001         5001\n",
       "6    70008    5760.00  2012-09-10         3005         5001\n",
       "7    70010    1983.43  2012-10-10         3001         5006\n",
       "8    70003    2480.40  2012-10-10         3005         5003\n",
       "9    70012     250.45  2012-06-27         3001         5002\n",
       "10   70011      75.29  2012-08-17         3005         5007\n",
       "11   70013    3045.60  2012-04-25         3005         5001"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['2012-10-05','2012-09-10','2012-10-05','2012-08-17','2012-09-10','2012-07-27','2012-09-10','2012-10-10','2012-10-10','2012-06-27','2012-08-17','2012-04-25'],\n",
    "'customer_id':[3001,3001,3005,3001,3005,3001,3005,3001,3005,3001,3005,3005],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Group on 'customer_id' and display the list of order dates in group wise:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "customer_id\n",
       "3001    [2012-10-05, 2012-09-10, 2012-08-17, 2012-07-2...\n",
       "3005    [2012-10-05, 2012-09-10, 2012-09-10, 2012-10-1...\n",
       "Name: ord_date, dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df.groupby('customer_id')['ord_date'].apply(list)\n",
    "print(\"\\nGroup on 'customer_id' and display the list of order dates in group wise:\")\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3001</th>\n",
       "      <td>[2012-10-05, 2012-09-10, 2012-08-17, 2012-07-2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>[2012-10-05, 2012-09-10, 2012-09-10, 2012-10-1...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      ord_date\n",
       "customer_id                                                   \n",
       "3001         [2012-10-05, 2012-09-10, 2012-08-17, 2012-07-2...\n",
       "3005         [2012-10-05, 2012-09-10, 2012-09-10, 2012-10-1..."
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups and calculate monthly purchase amount.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>09-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>08-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>07-27-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>06-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>07-08-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>04-25-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  05-10-2012         3001         5002\n",
       "1    70009     270.65  09-10-2012         3001         5005\n",
       "2    70002      65.26  05-10-2012         3005         5001\n",
       "3    70004     110.50  08-17-2012         3001         5003\n",
       "4    70007     948.50  10-09-2012         3005         5002\n",
       "5    70005    2400.60  07-27-2012         3001         5001\n",
       "6    70008    5760.00  10-09-2012         3005         5001\n",
       "7    70010    1983.43  10-10-2012         3001         5006\n",
       "8    70003    2480.40  10-10-2012         3005         5003\n",
       "9    70012     250.45  06-17-2012         3001         5002\n",
       "10   70011      75.29  07-08-2012         3005         5007\n",
       "11   70013    3045.60  04-25-2012         3005         5001"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['05-10-2012','09-10-2012','05-10-2012','08-17-2012','10-09-2012','07-27-2012','10-09-2012','10-10-2012','10-10-2012','06-17-2012','07-08-2012','04-25-2012'],\n",
    "'customer_id':[3001,3001,3005,3001,3005,3001,3005,3001,3005,3001,3005,3005],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>2012-05-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>2012-05-10</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>2012-10-09</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2012-07-27</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>2012-10-09</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>2012-06-17</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>2012-07-08</td>\n",
       "      <td>3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>2012-04-25</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt   ord_date  customer_id  salesman_id\n",
       "0    70001     150.50 2012-05-10         3001         5002\n",
       "1    70009     270.65 2012-09-10         3001         5005\n",
       "2    70002      65.26 2012-05-10         3005         5001\n",
       "3    70004     110.50 2012-08-17         3001         5003\n",
       "4    70007     948.50 2012-10-09         3005         5002\n",
       "5    70005    2400.60 2012-07-27         3001         5001\n",
       "6    70008    5760.00 2012-10-09         3005         5001\n",
       "7    70010    1983.43 2012-10-10         3001         5006\n",
       "8    70003    2480.40 2012-10-10         3005         5003\n",
       "9    70012     250.45 2012-06-17         3001         5002\n",
       "10   70011      75.29 2012-07-08         3005         5007\n",
       "11   70013    3045.60 2012-04-25         3005         5001"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['ord_date']= pd.to_datetime(df['ord_date']) \n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Month wise purchase amount:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>customer_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ord_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-04-30</th>\n",
       "      <td>3045.60</td>\n",
       "      <td>[3005]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-05-31</th>\n",
       "      <td>215.76</td>\n",
       "      <td>[3001, 3005]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-06-30</th>\n",
       "      <td>250.45</td>\n",
       "      <td>[3001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-07-31</th>\n",
       "      <td>2475.89</td>\n",
       "      <td>[3005, 3001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-08-31</th>\n",
       "      <td>110.50</td>\n",
       "      <td>[3001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-09-30</th>\n",
       "      <td>270.65</td>\n",
       "      <td>[3001]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-10-31</th>\n",
       "      <td>11172.33</td>\n",
       "      <td>[3005, 3005, 3001, 3005]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            purch_amt               customer_id\n",
       "ord_date                                       \n",
       "2012-04-30    3045.60                    [3005]\n",
       "2012-05-31     215.76              [3001, 3005]\n",
       "2012-06-30     250.45                    [3001]\n",
       "2012-07-31    2475.89              [3005, 3001]\n",
       "2012-08-31     110.50                    [3001]\n",
       "2012-09-30     270.65                    [3001]\n",
       "2012-10-31   11172.33  [3005, 3005, 3001, 3005]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\nMonth wise purchase amount:\")\n",
    "result = df.set_index('ord_date').groupby(pd.Grouper(freq='M')).agg({'purch_amt':sum,'customer_id':list})\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups, group by month and year based on order date and find the total purchase amount year wise, month wise.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>09-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>05-10-2013</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>08-17-2013</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>10-09-2013</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>07-27-2014</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>06-17-2014</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>07-08-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>04-25-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  05-10-2012         3001         5002\n",
       "1    70009     270.65  09-10-2012         3001         5005\n",
       "2    70002      65.26  05-10-2013         3005         5001\n",
       "3    70004     110.50  08-17-2013         3001         5003\n",
       "4    70007     948.50  10-09-2013         3005         5002\n",
       "5    70005    2400.60  07-27-2014         3001         5001\n",
       "6    70008    5760.00  10-09-2012         3005         5001\n",
       "7    70010    1983.43  10-10-2012         3001         5006\n",
       "8    70003    2480.40  10-10-2012         3005         5003\n",
       "9    70012     250.45  06-17-2014         3001         5002\n",
       "10   70011      75.29  07-08-2012         3005         5007\n",
       "11   70013    3045.60  04-25-2012         3005         5001"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['05-10-2012','09-10-2012','05-10-2013','08-17-2013','10-09-2013','07-27-2014','10-09-2012','10-10-2012','10-10-2012','06-17-2014','07-08-2012','04-25-2012'],\n",
    "'customer_id':[3001,3001,3005,3001,3005,3001,3005,3001,3005,3001,3005,3005],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Year wise Month wise purchase amount:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>purch_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ord_date</th>\n",
       "      <th>ord_date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2012</th>\n",
       "      <th>4</th>\n",
       "      <td>3045.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>150.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>75.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>270.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10223.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2013</th>\n",
       "      <th>5</th>\n",
       "      <td>65.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>110.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>948.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2014</th>\n",
       "      <th>6</th>\n",
       "      <td>250.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2400.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   purch_amt\n",
       "ord_date ord_date           \n",
       "2012     4           3045.60\n",
       "         5            150.50\n",
       "         7             75.29\n",
       "         9            270.65\n",
       "         10         10223.83\n",
       "2013     5             65.26\n",
       "         8            110.50\n",
       "         10           948.50\n",
       "2014     6            250.45\n",
       "         7           2400.60"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['ord_date']= pd.to_datetime(df['ord_date']) \n",
    "print(\"\\nYear wise Month wise purchase amount:\")\n",
    "result = df.groupby([df['ord_date'].dt.year, df['ord_date'].dt.month]).agg({'purch_amt':sum})\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups based on first column and set other column values into a list of values.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>Z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>21</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>30</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    X   Y   Z\n",
       "0  10  10  22\n",
       "1  10  15  20\n",
       "2  10  11  18\n",
       "3  20  20  20\n",
       "4  30  21  13\n",
       "5  30  12  10\n",
       "6  10  14   0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'X' : [10, 10, 10, 20, 30, 30, 10], \n",
    "                    'Y' : [10, 15, 11, 20, 21, 12, 14], \n",
    "                    'Z' : [22, 20, 18, 20, 13, 10, 0]})\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Y</th>\n",
       "      <th>Z</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>X</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>[10, 15, 11, 14]</td>\n",
       "      <td>[22, 20, 18, 0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>[20]</td>\n",
       "      <td>[20]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>[21, 12]</td>\n",
       "      <td>[13, 10]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Y                Z\n",
       "X                                    \n",
       "10  [10, 15, 11, 14]  [22, 20, 18, 0]\n",
       "20              [20]             [20]\n",
       "30          [21, 12]         [13, 10]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result= df.groupby('X').aggregate(lambda tdf: tdf.unique().tolist())\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups based on all columns and calculate GroupBy value counts on the dataframe.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>type</th>\n",
       "      <th>book</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  type     book\n",
       "0   1    10     Math\n",
       "1   2    15  English\n",
       "2   1    11  Physics\n",
       "3   1    20     Math\n",
       "4   2    21  English\n",
       "5   1    12  Physics\n",
       "6   2    14  English"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame( {'id' : [1, 2, 1, 1, 2, 1, 2], \n",
    "                    'type' : [10, 15, 11, 20, 21, 12, 14], \n",
    "                    'book' : ['Math','English','Physics','Math','English','Physics','English']})\n",
    "\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book</th>\n",
       "      <th>English</th>\n",
       "      <th>Math</th>\n",
       "      <th>Physics</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">1</th>\n",
       "      <th>10</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2</th>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "book     English  Math  Physics\n",
       "id type                        \n",
       "1  10          0     1        0\n",
       "   11          0     0        1\n",
       "   12          0     0        1\n",
       "   20          0     1        0\n",
       "2  14          1     0        0\n",
       "   15          1     0        0\n",
       "   21          1     0        0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df.groupby(['id', 'type', 'book']).size().unstack(fill_value=0)\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups and count unique values of 'value' column.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id value\n",
       "0   1     a\n",
       "1   1     a\n",
       "2   2     b\n",
       "3   3  None\n",
       "4   3     a\n",
       "5   4     a\n",
       "6   4  None\n",
       "7   4     b"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'id': [1, 1, 2, 3, 3, 4, 4, 4],\n",
    "    'value': ['a', 'a', 'b', None, 'a', 'a', None, 'b']\n",
    "})\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Count unique values:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "value\n",
       "a    3\n",
       "b    2\n",
       "Name: id, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Count unique values:\")\n",
    "df.groupby('value')['id'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataframe into groups and list all the keys from the GroupBy object.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151      31   \n",
       "S6        s004    VI    David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s004'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'height': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "List of all the keys:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "dict_keys(['s001', 's002', 's003', 's004'])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp = df.groupby('school_code')\n",
    "print(\"\\nList of all the keys:\")\n",
    "gp.groups.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataframe into groups and create a new column with count from GroupBy.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book_name</th>\n",
       "      <th>book_type</th>\n",
       "      <th>book_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Book1</td>\n",
       "      <td>Math</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Book2</td>\n",
       "      <td>Physics</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Book3</td>\n",
       "      <td>Computer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Book4</td>\n",
       "      <td>Science</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Book1</td>\n",
       "      <td>Math</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Book2</td>\n",
       "      <td>Physics</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Book3</td>\n",
       "      <td>Computer</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Book5</td>\n",
       "      <td>English</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  book_name book_type  book_id\n",
       "0     Book1      Math        1\n",
       "1     Book2   Physics        2\n",
       "2     Book3  Computer        3\n",
       "3     Book4   Science        4\n",
       "4     Book1      Math        1\n",
       "5     Book2   Physics        2\n",
       "6     Book3  Computer        3\n",
       "7     Book5   English        5"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'book_name':['Book1','Book2','Book3','Book4','Book1','Book2','Book3','Book5'],\n",
    "'book_type':['Math','Physics','Computer','Science','Math','Physics','Computer','English'],\n",
    "'book_id':[1,2,3,4,1,2,3,5]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "New column with count from groupby:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>book_name</th>\n",
       "      <th>book_type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Book1</td>\n",
       "      <td>Math</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Book2</td>\n",
       "      <td>Physics</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Book3</td>\n",
       "      <td>Computer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Book4</td>\n",
       "      <td>Science</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Book5</td>\n",
       "      <td>English</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  book_name book_type  count\n",
       "0     Book1      Math      2\n",
       "1     Book2   Physics      2\n",
       "2     Book3  Computer      2\n",
       "3     Book4   Science      1\n",
       "4     Book5   English      1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\nNew column with count from groupby:\")\n",
    "result = df.groupby([\"book_name\", \"book_type\"])[\"book_type\"].count().reset_index(name=\"count\")\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataframe into groups with bin counts.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>sales_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>3002</td>\n",
       "      <td>5004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>3009</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>3007</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>3002</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>3004</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>3009</td>\n",
       "      <td>5008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>3008</td>\n",
       "      <td>5004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>3003</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt  customer_id  sales_id\n",
       "0    70001     150.50         3005      5002\n",
       "1    70009     270.65         3001      5003\n",
       "2    70002      65.26         3002      5004\n",
       "3    70004     110.50         3009      5003\n",
       "4    70007     948.50         3005      5002\n",
       "5    70005    2400.60         3007      5001\n",
       "6    70008    5760.00         3002      5005\n",
       "7    70010    1983.43         3004      5007\n",
       "8    70003    2480.40         3009      5008\n",
       "9    70012     250.45         3008      5004\n",
       "10   70011      75.29         3003      5005\n",
       "11   70013    3045.60         3002      5001"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'customer_id':[3005,3001,3002,3009,3005,3007,3002,3004,3009,3008,3003,3002],\n",
    "'sales_id':[5002,5003,5004,5003,5002,5001,5005,5007,5008,5004,5005,5001]})\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>sales_id</th>\n",
       "      <th>(5000.993, 5003.333]</th>\n",
       "      <th>(5003.333, 5005.667]</th>\n",
       "      <th>(5005.667, 5008.0]</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3001</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3002</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3008</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "sales_id     (5000.993, 5003.333]  (5003.333, 5005.667]  (5005.667, 5008.0]\n",
       "customer_id                                                                \n",
       "3001                            1                     0                   0\n",
       "3002                            1                     2                   0\n",
       "3003                            0                     1                   0\n",
       "3004                            0                     0                   1\n",
       "3005                            2                     0                   0\n",
       "3007                            1                     0                   0\n",
       "3008                            0                     1                   0\n",
       "3009                            1                     0                   1"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups = df.groupby(['customer_id', pd.cut(df.sales_id, 3)])\n",
    "result = groups.size().unstack()\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataframe into groups with multiple aggregations.\n",
    "Split the following given dataframe by school code, class and get mean, min, and max value of height and age for each value of the school."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151      31   \n",
       "S6        s001    VI    David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s001'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'height': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">height</th>\n",
       "      <th colspan=\"3\" halign=\"left\">weight</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "      <th>sum</th>\n",
       "      <th>min</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">s001</th>\n",
       "      <th>V</th>\n",
       "      <td>173</td>\n",
       "      <td>173.0</td>\n",
       "      <td>35</td>\n",
       "      <td>35</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VI</th>\n",
       "      <td>167</td>\n",
       "      <td>163.0</td>\n",
       "      <td>62</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s002</th>\n",
       "      <th>V</th>\n",
       "      <td>192</td>\n",
       "      <td>171.5</td>\n",
       "      <td>63</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s003</th>\n",
       "      <th>VI</th>\n",
       "      <td>186</td>\n",
       "      <td>186.0</td>\n",
       "      <td>33</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  height        weight          \n",
       "                     max   mean    sum min count\n",
       "school_code class                               \n",
       "s001        V        173  173.0     35  35     1\n",
       "            VI       167  163.0     62  30     2\n",
       "s002        V        192  171.5     63  31     2\n",
       "s003        VI       186  186.0     33  33     1"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df.groupby(['school_code','class']).agg({'height': ['max', 'mean'],\n",
    "                                 'weight': ['sum','min','count']})\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataframe into groups and display target column as a list of unique values.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>type</th>\n",
       "      <th>book</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  type     book\n",
       "0   A     1     Math\n",
       "1   A     1     Math\n",
       "2   A     1  English\n",
       "3   A     1  Physics\n",
       "4   A     2     Math\n",
       "5   A     2  English\n",
       "6   B     1  Physics\n",
       "7   B     1  English\n",
       "8   B     1  Physics\n",
       "9   B     2  English\n",
       "10  B     2  English"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame( {'id' : ['A','A','A','A','A','A','B','B','B','B','B'], \n",
    "                    'type' : [1,1,1,1,2,2,1,1,1,2,2], \n",
    "                    'book' : ['Math','Math','English','Physics','Math','English','Physics','English','Physics','English','English']})\n",
    "\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>type</th>\n",
       "      <th>book</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>[Math, English, Physics]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "      <td>[Math, English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>[Physics, English]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>[English]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  id  type                      book\n",
       "0  A     1  [Math, English, Physics]\n",
       "1  A     2           [Math, English]\n",
       "2  B     1        [Physics, English]\n",
       "3  B     2                 [English]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df = df[['id', 'type', 'book']].drop_duplicates()\\\n",
    "                         .groupby(['id','type'])['book']\\\n",
    "                         .apply(list)\\\n",
    "                         .reset_index()\n",
    "new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups and calculate quarterly purchase amount.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>09-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>08-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>07-27-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>06-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>07-08-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>04-25-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  05-10-2012         3001         5002\n",
       "1    70009     270.65  09-10-2012         3001         5005\n",
       "2    70002      65.26  05-10-2012         3005         5001\n",
       "3    70004     110.50  08-17-2012         3001         5003\n",
       "4    70007     948.50  10-09-2012         3005         5002\n",
       "5    70005    2400.60  07-27-2012         3001         5001\n",
       "6    70008    5760.00  10-09-2012         3005         5001\n",
       "7    70010    1983.43  10-10-2012         3001         5006\n",
       "8    70003    2480.40  10-10-2012         3005         5003\n",
       "9    70012     250.45  06-17-2012         3001         5002\n",
       "10   70011      75.29  07-08-2012         3005         5007\n",
       "11   70013    3045.60  04-25-2012         3005         5001"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['05-10-2012','09-10-2012','05-10-2012','08-17-2012','10-09-2012','07-27-2012','10-09-2012','10-10-2012','10-10-2012','06-17-2012','07-08-2012','04-25-2012'],\n",
    "'customer_id':[3001,3001,3005,3001,3005,3001,3005,3001,3005,3001,3005,3005],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Quartly purchase amount:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>purch_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ord_date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-30</th>\n",
       "      <td>3511.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-09-30</th>\n",
       "      <td>2857.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-12-31</th>\n",
       "      <td>11172.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            purch_amt\n",
       "ord_date             \n",
       "2012-06-30    3511.81\n",
       "2012-09-30    2857.04\n",
       "2012-12-31   11172.33"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['ord_date']= pd.to_datetime(df['ord_date']) \n",
    "print(\"\\nQuartly purchase amount:\")\n",
    "result = df.set_index('ord_date').groupby(pd.Grouper(freq='Q')).agg({'purch_amt':sum})\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataframe into groups by school code and get mean, min, and max value of age with customized column name for each school.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age   height   weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12       173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12       192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13       186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13       167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14       151      31   \n",
       "S6        s004    VI    David Parkes     15/09/1997   12       159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student_data = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s004'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    ' height ': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "\n",
    "print(\"Original DataFrame:\")\n",
    "student_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age_Mean</th>\n",
       "      <th>Age_Max</th>\n",
       "      <th>Age_Min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>s001</th>\n",
       "      <td>12.5</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s002</th>\n",
       "      <td>13.0</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s003</th>\n",
       "      <td>13.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s004</th>\n",
       "      <td>12.0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Age_Mean  Age_Max  Age_Min\n",
       "school_code                            \n",
       "s001             12.5       13       12\n",
       "s002             13.0       14       12\n",
       "s003             13.0       13       13\n",
       "s004             12.0       12       12"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_single = student_data.groupby('school_code').agg(Age_Mean = ('age','mean'),Age_Max=('age',max),Age_Min=('age',min))\n",
    "grouped_single"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following datasets into groups on customer id and calculate the number of customers starting with 'C', the list of all products and the difference of maximum purchase amount and minimum purchase amount.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>C3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>09-10-2012</td>\n",
       "      <td>C3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>D3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>08-17-2012</td>\n",
       "      <td>D3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>C3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>07-27-2012</td>\n",
       "      <td>D3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>C3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>D3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>D3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>06-17-2012</td>\n",
       "      <td>C3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>07-08-2012</td>\n",
       "      <td>D3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>04-25-2012</td>\n",
       "      <td>D3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date customer_id  salesman_id\n",
       "0    70001     150.50  05-10-2012       C3001         5002\n",
       "1    70009     270.65  09-10-2012       C3001         5005\n",
       "2    70002      65.26  05-10-2012       D3005         5001\n",
       "3    70004     110.50  08-17-2012       D3001         5003\n",
       "4    70007     948.50  10-09-2012       C3005         5002\n",
       "5    70005    2400.60  07-27-2012       D3001         5001\n",
       "6    70008    5760.00  10-09-2012       C3005         5001\n",
       "7    70010    1983.43  10-10-2012       D3001         5006\n",
       "8    70003    2480.40  10-10-2012       D3005         5003\n",
       "9    70012     250.45  06-17-2012       C3001         5002\n",
       "10   70011      75.29  07-08-2012       D3005         5007\n",
       "11   70013    3045.60  04-25-2012       D3005         5001"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5, 270.65, 65.26, 110.5, 948.5, 2400.6, 5760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'ord_date': ['05-10-2012','09-10-2012','05-10-2012','08-17-2012','10-09-2012','07-27-2012','10-09-2012','10-10-2012','10-10-2012','06-17-2012','07-08-2012','04-25-2012'],\n",
    "'customer_id':['C3001','C3001','D3005','D3001','C3005','D3001','C3005','D3001','D3005','C3001','D3005','D3005'],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Number of customers  starting with ‘C’, the list of all products and the difference of maximum purchase amount and minimum purchase amount:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id_start_C</th>\n",
       "      <th>customer_id_list</th>\n",
       "      <th>purchase_amt_gap</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>salesman_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5001</th>\n",
       "      <td>1</td>\n",
       "      <td>D3005, D3001, C3005, D3005</td>\n",
       "      <td>5694.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>3</td>\n",
       "      <td>C3001, C3005, C3001</td>\n",
       "      <td>798.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>0</td>\n",
       "      <td>D3001, D3005</td>\n",
       "      <td>2369.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5005</th>\n",
       "      <td>1</td>\n",
       "      <td>C3001</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5006</th>\n",
       "      <td>0</td>\n",
       "      <td>D3001</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5007</th>\n",
       "      <td>0</td>\n",
       "      <td>D3005</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             customer_id_start_C            customer_id_list  purchase_amt_gap\n",
       "salesman_id                                                                   \n",
       "5001                           1  D3005, D3001, C3005, D3005           5694.74\n",
       "5002                           3         C3001, C3005, C3001            798.00\n",
       "5003                           0                D3001, D3005           2369.90\n",
       "5005                           1                       C3001              0.00\n",
       "5006                           0                       D3001              0.00\n",
       "5007                           0                       D3005              0.00"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def customer_id_C(x):\n",
    "    return (x.str[0] == 'C').sum()\n",
    "\n",
    "\n",
    "\n",
    "result = df.groupby(['salesman_id'])\\\n",
    "  .agg(customer_id_start_C = ('customer_id', customer_id_C),\n",
    "       customer_id_list = ('customer_id', lambda x: ', '.join(x)),\n",
    "       purchase_amt_gap   = ('purch_amt', lambda x: x.max()-x.min())\n",
    "      )\n",
    "print(\"\\nNumber of customers  starting with ‘C’, the list of all products and the difference of maximum purchase amount and minimum purchase amount:\")\n",
    "result\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>09-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>08-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>07-27-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>06-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>07-08-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>04-25-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  05-10-2012         3001         5002\n",
       "1    70009     270.65  09-10-2012         3001         5005\n",
       "2    70002      65.26  05-10-2012         3005         5001\n",
       "3    70004     110.50  08-17-2012         3001         5003\n",
       "4    70007     948.50  10-09-2012         3005         5002\n",
       "5    70005    2400.60  07-27-2012         3001         5001\n",
       "6    70008    5760.00  10-09-2012         3005         5001\n",
       "7    70010    1983.43  10-10-2012         3001         5006\n",
       "8    70003    2480.40  10-10-2012         3005         5003\n",
       "9    70012     250.45  06-17-2012         3001         5002\n",
       "10   70011      75.29  07-08-2012         3005         5007\n",
       "11   70013    3045.60  04-25-2012         3005         5001"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['05-10-2012','09-10-2012','05-10-2012','08-17-2012','10-09-2012','07-27-2012','10-09-2012','10-10-2012','10-10-2012','06-17-2012','07-08-2012','04-25-2012'],\n",
    "'customer_id':[3001,3001,3005,3001,3005,3001,3005,3001,3005,3001,3005,3005],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Percentage of purch_amt in each group of customer_id:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>% (Purch Amt.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">3001</th>\n",
       "      <th>5001</th>\n",
       "      <td>2400.60</td>\n",
       "      <td>13.685510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>400.95</td>\n",
       "      <td>2.285764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>110.50</td>\n",
       "      <td>0.629946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5005</th>\n",
       "      <td>270.65</td>\n",
       "      <td>1.542941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5006</th>\n",
       "      <td>1983.43</td>\n",
       "      <td>11.307278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">3005</th>\n",
       "      <th>5001</th>\n",
       "      <td>8870.86</td>\n",
       "      <td>50.571626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>948.50</td>\n",
       "      <td>5.407276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>2480.40</td>\n",
       "      <td>14.140440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5007</th>\n",
       "      <td>75.29</td>\n",
       "      <td>0.429219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         purch_amt  % (Purch Amt.)\n",
       "customer_id salesman_id                           \n",
       "3001        5001           2400.60       13.685510\n",
       "            5002            400.95        2.285764\n",
       "            5003            110.50        0.629946\n",
       "            5005            270.65        1.542941\n",
       "            5006           1983.43       11.307278\n",
       "3005        5001           8870.86       50.571626\n",
       "            5002            948.50        5.407276\n",
       "            5003           2480.40       14.140440\n",
       "            5007             75.29        0.429219"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr_data = df.groupby(['customer_id','salesman_id']).agg({'purch_amt': 'sum'})\n",
    "gr_data[\"% (Purch Amt.)\"] = gr_data.apply(lambda x:  100*x / x.sum())\n",
    "print(\"\\nPercentage of purch_amt in each group of customer_id:\")\n",
    "gr_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a dataset, group by one column and get mean, min, and max values by group, also change the column name of the aggregated metric. Using the following dataset find the mean, min, and max values of purchase amount (purch_amt) group by customer id (customer_id).\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151      31   \n",
       "S6        s004    VI    David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s004'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'height': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Change the name of an aggregated metric:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean_age</th>\n",
       "      <th>min_age</th>\n",
       "      <th>max_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>s001</th>\n",
       "      <td>12.5</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s002</th>\n",
       "      <td>13.0</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s003</th>\n",
       "      <td>13.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s004</th>\n",
       "      <td>12.0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 age                \n",
       "            mean_age min_age max_age\n",
       "school_code                         \n",
       "s001            12.5      12      13\n",
       "s002            13.0      12      14\n",
       "s003            13.0      13      13\n",
       "s004            12.0      12      12"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('\\nChange the name of an aggregated metric:')\n",
    "grouped_single = df.groupby('school_code').agg({'age': [(\"mean_age\",\"mean\"), (\"min_age\", \"min\"), (\"max_age\",\"max\")]})\n",
    "grouped_single"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataset, group by two columns and convert other columns of the dataframe into a dictionary with column header as key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151      31   \n",
       "S6        s004    VI    David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s004'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'height': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'school_code': 's001',\n",
       "  'class': 'V',\n",
       "  'other_columns': [{'name': 'Alberto Franco',\n",
       "    'date_Of_Birth ': '15/05/2002',\n",
       "    'age': 12,\n",
       "    'height': 173,\n",
       "    'weight': 35,\n",
       "    'address': 'street1'}]},\n",
       " {'school_code': 's001',\n",
       "  'class': 'VI',\n",
       "  'other_columns': [{'name': 'Eesha Hinton',\n",
       "    'date_Of_Birth ': '25/09/1998',\n",
       "    'age': 13,\n",
       "    'height': 167,\n",
       "    'weight': 30,\n",
       "    'address': 'street1'}]},\n",
       " {'school_code': 's002',\n",
       "  'class': 'V',\n",
       "  'other_columns': [{'name': 'Gino Mcneill',\n",
       "    'date_Of_Birth ': '17/05/2002',\n",
       "    'age': 12,\n",
       "    'height': 192,\n",
       "    'weight': 32,\n",
       "    'address': 'street2'},\n",
       "   {'name': 'Gino Mcneill',\n",
       "    'date_Of_Birth ': '11/05/2002',\n",
       "    'age': 14,\n",
       "    'height': 151,\n",
       "    'weight': 31,\n",
       "    'address': 'street2'}]},\n",
       " {'school_code': 's003',\n",
       "  'class': 'VI',\n",
       "  'other_columns': [{'name': 'Ryan Parkes',\n",
       "    'date_Of_Birth ': '16/02/1999',\n",
       "    'age': 13,\n",
       "    'height': 186,\n",
       "    'weight': 33,\n",
       "    'address': 'street3'}]},\n",
       " {'school_code': 's004',\n",
       "  'class': 'VI',\n",
       "  'other_columns': [{'name': 'David Parkes',\n",
       "    'date_Of_Birth ': '15/09/1997',\n",
       "    'age': 12,\n",
       "    'height': 159,\n",
       "    'weight': 32,\n",
       "    'address': 'street4'}]}]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_data_list = list()\n",
    "\n",
    "for gg, dd in df.groupby(['school_code','class']):\n",
    "    group = dict(zip(['school_code','class'], gg))\n",
    "    ocolumns_list = list()\n",
    "    for _, data in dd.iterrows():\n",
    "        data = data.drop(labels=['school_code','class'])\n",
    "        ocolumns_list.append(data.to_dict())\n",
    "    group['other_columns'] = ocolumns_list\n",
    "    dict_data_list.append(group)\n",
    "\n",
    "dict_data_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataset, group by one column and apply an aggregate function to few columns and another aggregate function to the rest of the columns of the dataframe.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>salesman_id</th>\n",
       "      <th>sale_jan</th>\n",
       "      <th>sale_feb</th>\n",
       "      <th>sale_mar</th>\n",
       "      <th>sale_apr</th>\n",
       "      <th>sale_may</th>\n",
       "      <th>sale_jun</th>\n",
       "      <th>sale_jul</th>\n",
       "      <th>sale_aug</th>\n",
       "      <th>sale_sep</th>\n",
       "      <th>sale_oct</th>\n",
       "      <th>sale_nov</th>\n",
       "      <th>sale_dec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5002</td>\n",
       "      <td>150.50</td>\n",
       "      <td>250.50</td>\n",
       "      <td>150.50</td>\n",
       "      <td>150.50</td>\n",
       "      <td>130.50</td>\n",
       "      <td>150.50</td>\n",
       "      <td>950.50</td>\n",
       "      <td>150.50</td>\n",
       "      <td>150.50</td>\n",
       "      <td>150.50</td>\n",
       "      <td>150.50</td>\n",
       "      <td>150.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5005</td>\n",
       "      <td>270.65</td>\n",
       "      <td>170.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>70.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>70.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5001</td>\n",
       "      <td>65.26</td>\n",
       "      <td>15.26</td>\n",
       "      <td>65.26</td>\n",
       "      <td>95.26</td>\n",
       "      <td>65.26</td>\n",
       "      <td>45.26</td>\n",
       "      <td>65.26</td>\n",
       "      <td>65.26</td>\n",
       "      <td>65.26</td>\n",
       "      <td>65.26</td>\n",
       "      <td>95.26</td>\n",
       "      <td>65.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5003</td>\n",
       "      <td>110.50</td>\n",
       "      <td>110.50</td>\n",
       "      <td>110.50</td>\n",
       "      <td>210.50</td>\n",
       "      <td>310.50</td>\n",
       "      <td>110.50</td>\n",
       "      <td>210.50</td>\n",
       "      <td>110.50</td>\n",
       "      <td>110.50</td>\n",
       "      <td>110.50</td>\n",
       "      <td>110.50</td>\n",
       "      <td>110.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5002</td>\n",
       "      <td>948.50</td>\n",
       "      <td>598.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "      <td>948.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5001</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>1400.60</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>3400.60</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>400.60</td>\n",
       "      <td>200.60</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2400.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5001</td>\n",
       "      <td>1760.00</td>\n",
       "      <td>2760.00</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>760.00</td>\n",
       "      <td>760.00</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>5760.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5006</td>\n",
       "      <td>2983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>983.43</td>\n",
       "      <td>983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5003</td>\n",
       "      <td>480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2480.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5002</td>\n",
       "      <td>1250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "      <td>250.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5007</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5001</td>\n",
       "      <td>1045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3045.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    salesman_id  sale_jan  sale_feb  sale_mar  sale_apr  sale_may  sale_jun  \\\n",
       "0          5002    150.50    250.50    150.50    150.50    130.50    150.50   \n",
       "1          5005    270.65    170.65    270.65    270.65    270.65    270.65   \n",
       "2          5001     65.26     15.26     65.26     95.26     65.26     45.26   \n",
       "3          5003    110.50    110.50    110.50    210.50    310.50    110.50   \n",
       "4          5002    948.50    598.50    948.50    948.50    948.50    948.50   \n",
       "5          5001   2400.60   1400.60   2400.60   2400.60   2400.60   3400.60   \n",
       "6          5001   1760.00   2760.00   5760.00    760.00    760.00   5760.00   \n",
       "7          5006   2983.43   1983.43   1983.43   1983.43   1983.43    983.43   \n",
       "8          5003    480.40   2480.40   2480.40   2480.40   2480.40   2480.40   \n",
       "9          5002   1250.45    250.45    250.45    250.45    250.45    250.45   \n",
       "10         5007     75.29     75.29     75.29     75.29     75.29     75.29   \n",
       "11         5001   1045.60   3045.60   3045.60   3045.60   3045.60   3045.60   \n",
       "\n",
       "    sale_jul  sale_aug  sale_sep  sale_oct  sale_nov  sale_dec  \n",
       "0     950.50    150.50    150.50    150.50    150.50    150.50  \n",
       "1     270.65     70.65    270.65    270.65    270.65     70.65  \n",
       "2      65.26     65.26     65.26     65.26     95.26     65.26  \n",
       "3     210.50    110.50    110.50    110.50    110.50    110.50  \n",
       "4     948.50    948.50    948.50    948.50    948.50    948.50  \n",
       "5    2400.60    400.60    200.60   2400.60   2400.60   2400.60  \n",
       "6    5760.00   5760.00   5760.00   5760.00   5760.00   5760.00  \n",
       "7     983.43   1983.43   1983.43   1983.43   1983.43   1983.43  \n",
       "8    2480.40   2480.40   2480.40   2480.40   2480.40   2480.40  \n",
       "9     250.45    250.45    250.45    250.45    250.45    250.45  \n",
       "10     75.29     75.29     75.29     75.29     75.29     75.29  \n",
       "11   3045.60   3045.60   3045.60   3045.60   3045.60   3045.60  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001],\n",
    "'sale_jan':[150.5, 270.65, 65.26, 110.5, 948.5, 2400.6, 1760, 2983.43, 480.4,  1250.45, 75.29,1045.6],\n",
    "'sale_feb':[250.5, 170.65, 15.26, 110.5, 598.5, 1400.6, 2760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_mar':[150.5, 270.65, 65.26, 110.5, 948.5, 2400.6, 5760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_apr':[150.5, 270.65, 95.26, 210.5, 948.5, 2400.6, 760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_may':[130.5, 270.65, 65.26, 310.5, 948.5, 2400.6, 760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_jun':[150.5, 270.65, 45.26, 110.5, 948.5, 3400.6, 5760, 983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_jul':[950.5, 270.65, 65.26, 210.5, 948.5, 2400.6, 5760, 983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_aug':[150.5, 70.65,  65.26, 110.5, 948.5, 400.6, 5760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_sep':[150.5, 270.65, 65.26, 110.5, 948.5, 200.6, 5760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_oct':[150.5, 270.65, 65.26, 110.5, 948.5, 2400.6, 5760, 1983.43, 2480.4, 250.45, 75.29, 3045.6],\n",
    "'sale_nov':[150.5, 270.65, 95.26, 110.5, 948.5, 2400.6, 5760, 1983.43, 2480.4, 250.45, 75.29, 3045.6], \n",
    "'sale_dec':[150.5, 70.65, 65.26, 110.5, 948.5, 2400.6, 5760, 1983.43, 2480.4, 250.45, 75.29, 3045.6]\n",
    "})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\Result after group on salesman_id and apply different aggregate functions:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sale_jan</th>\n",
       "      <th>sale_feb</th>\n",
       "      <th>sale_mar</th>\n",
       "      <th>sale_apr</th>\n",
       "      <th>sale_may</th>\n",
       "      <th>sale_jun</th>\n",
       "      <th>sale_jul</th>\n",
       "      <th>sale_aug</th>\n",
       "      <th>sale_sep</th>\n",
       "      <th>sale_oct</th>\n",
       "      <th>sale_nov</th>\n",
       "      <th>sale_dec</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>salesman_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5001</th>\n",
       "      <td>5271.46</td>\n",
       "      <td>7221.46</td>\n",
       "      <td>11271.46</td>\n",
       "      <td>1575.365000</td>\n",
       "      <td>1567.865</td>\n",
       "      <td>3062.865000</td>\n",
       "      <td>2817.865000</td>\n",
       "      <td>2317.865000</td>\n",
       "      <td>2267.865000</td>\n",
       "      <td>2817.865000</td>\n",
       "      <td>2825.365000</td>\n",
       "      <td>2817.865000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>2349.45</td>\n",
       "      <td>1099.45</td>\n",
       "      <td>1349.45</td>\n",
       "      <td>449.816667</td>\n",
       "      <td>443.150</td>\n",
       "      <td>449.816667</td>\n",
       "      <td>716.483333</td>\n",
       "      <td>449.816667</td>\n",
       "      <td>449.816667</td>\n",
       "      <td>449.816667</td>\n",
       "      <td>449.816667</td>\n",
       "      <td>449.816667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>590.90</td>\n",
       "      <td>2590.90</td>\n",
       "      <td>2590.90</td>\n",
       "      <td>1345.450000</td>\n",
       "      <td>1395.450</td>\n",
       "      <td>1295.450000</td>\n",
       "      <td>1345.450000</td>\n",
       "      <td>1295.450000</td>\n",
       "      <td>1295.450000</td>\n",
       "      <td>1295.450000</td>\n",
       "      <td>1295.450000</td>\n",
       "      <td>1295.450000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5005</th>\n",
       "      <td>270.65</td>\n",
       "      <td>170.65</td>\n",
       "      <td>270.65</td>\n",
       "      <td>270.650000</td>\n",
       "      <td>270.650</td>\n",
       "      <td>270.650000</td>\n",
       "      <td>270.650000</td>\n",
       "      <td>70.650000</td>\n",
       "      <td>270.650000</td>\n",
       "      <td>270.650000</td>\n",
       "      <td>270.650000</td>\n",
       "      <td>70.650000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5006</th>\n",
       "      <td>2983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>1983.430000</td>\n",
       "      <td>1983.430</td>\n",
       "      <td>983.430000</td>\n",
       "      <td>983.430000</td>\n",
       "      <td>1983.430000</td>\n",
       "      <td>1983.430000</td>\n",
       "      <td>1983.430000</td>\n",
       "      <td>1983.430000</td>\n",
       "      <td>1983.430000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5007</th>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.29</td>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.290</td>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.290000</td>\n",
       "      <td>75.290000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             sale_jan  sale_feb  sale_mar     sale_apr  sale_may     sale_jun  \\\n",
       "salesman_id                                                                     \n",
       "5001          5271.46   7221.46  11271.46  1575.365000  1567.865  3062.865000   \n",
       "5002          2349.45   1099.45   1349.45   449.816667   443.150   449.816667   \n",
       "5003           590.90   2590.90   2590.90  1345.450000  1395.450  1295.450000   \n",
       "5005           270.65    170.65    270.65   270.650000   270.650   270.650000   \n",
       "5006          2983.43   1983.43   1983.43  1983.430000  1983.430   983.430000   \n",
       "5007            75.29     75.29     75.29    75.290000    75.290    75.290000   \n",
       "\n",
       "                sale_jul     sale_aug     sale_sep     sale_oct     sale_nov  \\\n",
       "salesman_id                                                                    \n",
       "5001         2817.865000  2317.865000  2267.865000  2817.865000  2825.365000   \n",
       "5002          716.483333   449.816667   449.816667   449.816667   449.816667   \n",
       "5003         1345.450000  1295.450000  1295.450000  1295.450000  1295.450000   \n",
       "5005          270.650000    70.650000   270.650000   270.650000   270.650000   \n",
       "5006          983.430000  1983.430000  1983.430000  1983.430000  1983.430000   \n",
       "5007           75.290000    75.290000    75.290000    75.290000    75.290000   \n",
       "\n",
       "                sale_dec  \n",
       "salesman_id               \n",
       "5001         2817.865000  \n",
       "5002          449.816667  \n",
       "5003         1295.450000  \n",
       "5005           70.650000  \n",
       "5006         1983.430000  \n",
       "5007           75.290000  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\Result after group on salesman_id and apply different aggregate functions:\")\n",
    "df = df.groupby('salesman_id').agg(lambda x : x.sum() if x.name in ['sale_jan','sale_feb','sale_mar'] else x.mean())\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataset, group by one column and remove those groups if all the values of a specific columns are not available.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>weight</th>\n",
       "      <th>height</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  weight  height\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173    35.0\n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192     NaN\n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186    33.0\n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167    30.0\n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151     NaN\n",
       "S6        s004    VI    David Parkes     15/09/1997   12     159    32.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s004'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'weight': [173, 192, 186, 167, 151, 159],\n",
    "    'height': [35, None, 33, 30, None, 32]},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataset using group by on specified column into two labels and ranges.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>salesman_id</th>\n",
       "      <th>sale_jan</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5001</td>\n",
       "      <td>150.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5002</td>\n",
       "      <td>270.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5003</td>\n",
       "      <td>65.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5004</td>\n",
       "      <td>110.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5005</td>\n",
       "      <td>948.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5006</td>\n",
       "      <td>2400.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5007</td>\n",
       "      <td>1760.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5008</td>\n",
       "      <td>2983.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5009</td>\n",
       "      <td>480.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5010</td>\n",
       "      <td>1250.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5011</td>\n",
       "      <td>75.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5012</td>\n",
       "      <td>1045.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    salesman_id  sale_jan\n",
       "0          5001    150.50\n",
       "1          5002    270.65\n",
       "2          5003     65.26\n",
       "3          5004    110.50\n",
       "4          5005    948.50\n",
       "5          5006   2400.60\n",
       "6          5007   1760.00\n",
       "7          5008   2983.43\n",
       "8          5009    480.40\n",
       "9          5010   1250.45\n",
       "10         5011     75.29\n",
       "11         5012   1045.60"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "df = pd.DataFrame({\n",
    "'salesman_id': [5001,5002,5003,5004,5005,5006,5007,5008,5009,5010,5011,5012],\n",
    "'sale_jan':[150.5, 270.65, 65.26, 110.5, 948.5, 2400.6, 1760, 2983.43, 480.4,  1250.45, 75.29,1045.6]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "GroupBy with condition of  two labels and ranges:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>salesman_id</th>\n",
       "      <th>sale_jan</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>3946.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>7595.17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  salesman_id  sale_jan\n",
       "0          S1   3946.01\n",
       "1          S2   7595.17"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df.groupby(pd.cut(df['salesman_id'], \n",
    "                  bins=[0,5006,np.inf],  \n",
    "                  labels=['S1', 'S2']))['sale_jan'].sum().reset_index()\n",
    "print(\"\\nGroupBy with condition of  two labels and ranges:\")\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataset using group by on first column and aggregate over multiple lists on second column.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_id</th>\n",
       "      <th>marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S001</td>\n",
       "      <td>[88, 89, 90]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S001</td>\n",
       "      <td>[78, 81, 60]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S002</td>\n",
       "      <td>[84, 83, 91]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S002</td>\n",
       "      <td>[84, 88, 91]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S003</td>\n",
       "      <td>[90, 89, 92]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S003</td>\n",
       "      <td>[88, 59, 90]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id         marks\n",
       "0       S001  [88, 89, 90]\n",
       "1       S001  [78, 81, 60]\n",
       "2       S002  [84, 83, 91]\n",
       "3       S002  [84, 88, 91]\n",
       "4       S003  [90, 89, 92]\n",
       "5       S003  [88, 59, 90]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'student_id': ['S001','S001','S002','S002','S003','S003'],\n",
    "    'marks': [[88,89,90],[78,81,60],[84,83,91],[84,88,91],[90,89,92],[88,59,90]]})\n",
    "print(\"Original DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>marks</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>student_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S001</th>\n",
       "      <td>[83.0, 85.0, 75.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S002</th>\n",
       "      <td>[84.0, 85.5, 91.0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S003</th>\n",
       "      <td>[89.0, 74.0, 91.0]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         marks\n",
       "student_id                    \n",
       "S001        [83.0, 85.0, 75.0]\n",
       "S002        [84.0, 85.5, 91.0]\n",
       "S003        [89.0, 74.0, 91.0]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df.set_index('student_id')['marks'].groupby('student_id').apply(list).apply(lambda x: np.mean(x,0))\n",
    "pd.DataFrame(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split the following dataset using group by on 'salesman_id' and find the first order date for each group.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3009</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2012-07-27</td>\n",
       "      <td>3007</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3004</td>\n",
       "      <td>5004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3009</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>2012-06-27</td>\n",
       "      <td>3008</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3003</td>\n",
       "      <td>5004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>2012-04-25</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  2012-10-05         3005         5002\n",
       "1    70009     270.65  2012-09-10         3001         5005\n",
       "2    70002      65.26  2012-10-05         3002         5001\n",
       "3    70004     110.50  2012-08-17         3009         5003\n",
       "4    70007     948.50  2012-09-10         3005         5002\n",
       "5    70005    2400.60  2012-07-27         3007         5001\n",
       "6    70008    5760.00  2012-09-10         3002         5001\n",
       "7    70010    1983.43  2012-10-10         3004         5004\n",
       "8    70003    2480.40  2012-10-10         3009         5003\n",
       "9    70012     250.45  2012-06-27         3008         5002\n",
       "10   70011      75.29  2012-08-17         3003         5004\n",
       "11   70013    3045.60  2012-04-25         3002         5001"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['2012-10-05','2012-09-10','2012-10-05','2012-08-17','2012-09-10','2012-07-27','2012-09-10','2012-10-10','2012-10-10','2012-06-27','2012-08-17','2012-04-25'],\n",
    "'customer_id':[3005,3001,3002,3009,3005,3007,3002,3004,3009,3008,3003,3002],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5004,5003,5002,5004,5001]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Groupby to find first order date for each group(salesman_id):\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "salesman_id\n",
       "5001    2012-04-25\n",
       "5002    2012-06-27\n",
       "5003    2012-08-17\n",
       "5004    2012-08-17\n",
       "5005    2012-09-10\n",
       "Name: ord_date, dtype: object"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\nGroupby to find first order date for each group(salesman_id):\")\n",
    "result = df.groupby('salesman_id')['ord_date'].min()\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to split a given dataset using group by on multiple columns and drop last n rows of from each group.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3002</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>2012-10-05</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3003</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3002</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>2012-07-27</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>2012-09-10</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3004</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>2012-10-10</td>\n",
       "      <td>3003</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>2012-06-27</td>\n",
       "      <td>3002</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>2012-08-17</td>\n",
       "      <td>3003</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>2012-04-25</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  2012-10-05         3002         5002\n",
       "1    70009     270.65  2012-09-10         3001         5003\n",
       "2    70002      65.26  2012-10-05         3001         5001\n",
       "3    70004     110.50  2012-08-17         3003         5003\n",
       "4    70007     948.50  2012-09-10         3002         5002\n",
       "5    70005    2400.60  2012-07-27         3002         5001\n",
       "6    70008    5760.00  2012-09-10         3001         5001\n",
       "7    70010    1983.43  2012-10-10         3004         5003\n",
       "8    70003    2480.40  2012-10-10         3003         5003\n",
       "9    70012     250.45  2012-06-27         3002         5002\n",
       "10   70011      75.29  2012-08-17         3003         5003\n",
       "11   70013    3045.60  2012-04-25         3001         5001"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['2012-10-05','2012-09-10','2012-10-05','2012-08-17','2012-09-10','2012-07-27','2012-09-10','2012-10-10','2012-10-10','2012-06-27','2012-08-17','2012-04-25'],\n",
    "'customer_id':[3002,3001,3001,3003,3002,3002,3001,3004,3003,3002,3003,3001],\n",
    "'salesman_id':[5002,5003,5001,5003,5002,5001,5001,5003,5003,5002,5003,5001]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
